package cn.ffcs.bpmpvc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.echoice.hibernate.HibernateEntityDao;
import org.echoice.paper.PageBean;
import org.echoice.util.DateUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import cn.ffcs.bpm.model.DateControl;
import cn.ffcs.bpm.model.ProcTranRecord;
import cn.ffcs.bpm.model.ProcessConfig;
import cn.ffcs.bpm.model.ProcessConfigDetail;
import cn.ffcs.bpm.model.Report;
import cn.ffcs.bpm.model.TransactProcessStatus;
import cn.ffcs.bpmpvc.dao.AppBusinessReqDao;
import cn.ffcs.bpmpvc.model.BpmAppInfo;
import cn.ffcs.bpmpvc.svc.DateManager;
import cn.ffcs.bpmpvc.svc.ProcessPvcService;
import cn.ffcs.bpmpvc.svc.TaskService;
import cn.ffcs.bpmpvc.web.forms.FlowReqReportForm;
import cn.ffcs.bpmpvc.web.forms.FlowTaskForm;
import cn.ffcs.bpmpvc.web.views.FlowTaskObjView;
import cn.ffcs.businessrequest.model.BusinessReason;
import cn.ffcs.businessrequest.model.BusinessRequest;
import cn.ffcs.businessrequest.model.DocContent;
import cn.ffcs.businessrequest.model.ProdAdvReq;
import cn.ffcs.businessrequest.model.ReqDelay;
import cn.ffcs.businessrequest.model.ReqDocu;
import cn.ffcs.businessrequest.model.ReqProgress;
import cn.ffcs.organ.model.Organization;
import cn.ffcs.organ.model.Person;
import cn.ffcs.security.model.User;
import cn.ffcs.system.dict.model.Dict;

public class AppBusinessReqDaoImpl extends HibernateEntityDao<BusinessRequest>
		implements AppBusinessReqDao {
	private JdbcTemplate jdbcTemplate;
	
	public BpmAppInfo getBpmAppInfo(String procinstId){
		String hql="select t from BpmAppInfo t where t.procinstId=?";
		List list=find(hql, new Object[]{procinstId});
		if(list!=null&&list.size()>0){
			return (BpmAppInfo)list.get(0);
		}
		return null;
	}
	@SuppressWarnings("unchecked")
	public List<Report> findNowOverReport(String type){
		List<Report> list=null;
		String sql="";
		if("01".endsWith(type)){
			sql="from Report t where t.dwNum=to_number(to_char(sysdate,'FMWW')) and t.type='"+type+"'";
		}else{
			sql="from Report t where t.dwNum=to_number(to_char(sysdate,'MM')) and t.type='"+type+"'";
		}
		list=getHibernateTemplate().find(sql);
		return list;
	}
	@SuppressWarnings("unchecked")
	public List<BusinessRequest> findWeekSetBsReq(String type){
		List<BusinessRequest> list=null;
		String sql="";
		if("week".endsWith(type)){
			sql=" from BusinessRequest t where t.weekState='01'";
			list=getHibernateTemplate().find(sql);
		}else if("month".equals(type)){
			sql=" from BusinessRequest t where t.monthState='01'";
			list=getHibernateTemplate().find(sql);
		}else{
			
		}
		return list;
	}
	@SuppressWarnings("unchecked")
	public List<Dict> findDict(String type) {
		// TODO Auto-generated method stub
		String hql = "select t from Dict t where t.dictType.keyValue=? and t.end>sysdate";
		List list = getHibernateTemplate().find(hql, type);
		return list;
	}

	@SuppressWarnings("unchecked")
	public Dict getDict(String name) {
		// TODO Auto-generated method stub
		String hql = "select t from Dict t where t.name=?";
		List list = getHibernateTemplate().find(hql, name);
		if (list != null && list.size() > 0) {
			return (Dict) list.get(0);
		}
		return null;
	}

	
	public Dict getDictByKey(String keyValue) {
		// TODO Auto-generated method stub
		String hql = "select t from Dict t where t.keyValue=?";
		List list = getHibernateTemplate().find(hql, keyValue);
		if (list != null && list.size() > 0) {
			return (Dict) list.get(0);
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public List<List<String>> listOverBs(String sql, final int size,
			final int num) {
		List<List<String>> list = getJdbcTemplate().query(sql, new Object[] {},
				new RowMapper() {
					public Object mapRow(ResultSet rs, int arg1)
							throws SQLException {
						List<String> list = new ArrayList<String>();
						String s = "";
						for (int i = 0; i < size + num; i++) {// 1
							s = rs.getString(i + 1);
							list.add(s);
						}
						return list;
					}
				});
		return list;
	}
	/**
	 * 按类型和部门统计需求单
	 * @param type
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<List<String>> listSumRequest(FlowReqReportForm flowReqReportForm){
		List<List<String>> list=new ArrayList<List<String>>();
		String type=flowReqReportForm.getType();
		String sql="";
		if("type".equals(type)){
			sql="select d.name as type,nvl(a.over,0),nvl(a.notover,0),nvl(a.taskcount,0)"
				+" from("
				+" select "
				+" t3.type as type"
				+" , sum( decode(nvl(to_char(t2.end_time,'yyyy-mm-dd'),0),'0',0,1) ) as over"
				+" , sum( decode(nvl(to_char(t2.end_time,'yyyy-mm-dd'),1),'1',1,0) ) as notover"
				+" ,count(t2.procinst_id) taskCount"
				+" from (select distinct tb.procinst_id,ta.end_time from t_bpm_transactprocessstatus ta,t_business_businessrequest tb"
				+" where ta.procinst_id=tb.procinst_id and ta.state!='active-join'"
				+" and tb.end>sysdate) t2,"
				+" t_business_businessrequest  t3"
				+" where t2.procinst_id = t3.procinst_id  "   
				//+" and t2.curr_process_config_detail is not null "
				+" and t3.end > sysdate ";
				if(StringUtils.isNotBlank(flowReqReportForm.getStartTime())){
					sql+=" and t3.begin>=to_date('"+flowReqReportForm.getStartTime()+"','yyyy-mm-dd')";
				}
				if(StringUtils.isNotBlank(flowReqReportForm.getEndTime())){
					sql+=" and t3.begin<=to_date('"+flowReqReportForm.getEndTime()+"','yyyy-mm-dd')";
				}
				;
			sql+=
				" group by t3.type"
				+" )a ,t_sys_dict d"
				+" where 1=1"
				+" and d.dict_type in(12,15)"
				+" and d.name=a.type(+)"
				+" union"
				+" select "
				+"'总计' as type"       
				+",sum( decode(nvl(to_char(t2.end_time,'yyyy-mm-dd'),0),'0',0,1) ) as over"
				+",sum( decode(nvl(to_char(t2.end_time,'yyyy-mm-dd'),1),'1',1,0) ) as notover"
				+",count(t2.procinst_id) taskCount"
				+" from (select distinct tb.procinst_id,ta.end_time from t_bpm_transactprocessstatus ta,t_business_businessrequest tb"
				+" where ta.procinst_id=tb.procinst_id and ta.state!='active-join'"
				+" and tb.end>sysdate) t2,"
				+"t_business_businessrequest  t3"
				+" where t2.procinst_id = t3.procinst_id"
				//+" and t2.curr_process_config_detail is not null"
				+" and t3.end > sysdate";
			if(StringUtils.isNotBlank(flowReqReportForm.getStartTime())){
				sql+=" and t3.begin>=to_date('"+flowReqReportForm.getStartTime()+"','yyyy-mm-dd')";
			}
			if(StringUtils.isNotBlank(flowReqReportForm.getEndTime())){
				sql+=" and t3.begin<=to_date('"+flowReqReportForm.getEndTime()+"','yyyy-mm-dd')";
			}
		}else if("dept".equals(type)){
			sql="select * from ("
				+" select distinct b.short_name,nvl(a.over,0),nvl(a.notover,0),nvl(a.taskcount,0)"
				+" from("
				+" select"
				+" t1.short_name"
				+",sum( decode(nvl(to_char(t2.end_time,'yyyy-mm-dd'),0),'0',0,1) ) as over"
				+",sum( decode(nvl(to_char(t2.end_time,'yyyy-mm-dd'),1),'1',1,0) ) as notover"
				+",count(t2.procinst_id) taskCount"
				+" from t_organ_organization t1,(select distinct tb.procinst_id, ta.end_time"
				+" from t_bpm_transactprocessstatus ta, t_business_businessrequest tb"
				+" where ta.procinst_id = tb.procinst_id"
				+" and ta.state != 'active-join'"
				+" and tb.end > sysdate) t2,"
				+"t_business_businessrequest t3"
				+" where t2.procinst_id = t3.procinst_id"
				+" and t3.start_dept_id=t1.id(+)"
				//+" and t2.curr_process_config_detail is not null"
				+" and t3.end > sysdate";
				if(StringUtils.isNotBlank(flowReqReportForm.getStartTime())){
					sql+=" and t3.begin>=to_date('"+flowReqReportForm.getStartTime()+"','yyyy-mm-dd')";
				}
				if(StringUtils.isNotBlank(flowReqReportForm.getEndTime())){
					sql+=" and t3.begin<=to_date('"+flowReqReportForm.getEndTime()+"','yyyy-mm-dd')";
				}
				;
			sql+=
				" group by  t1.short_name"
				+")a ,t_organ_organization b"
				+" where b.short_name=a.short_name(+)"
				+" and b.short_name is not null"
				+" union"
				+" select "
				+" '总计' as short_name"    
				+",sum( decode(nvl(to_char(t2.end_time,'yyyy-mm-dd'),0),'0',0,1) ) as over"
				+",sum( decode(nvl(to_char(t2.end_time,'yyyy-mm-dd'),1),'1',1,0) ) as notover"
				+",count(t2.procinst_id) taskCount"
				+" from (select distinct tb.procinst_id, ta.end_time"
				+" from t_bpm_transactprocessstatus ta, t_business_businessrequest tb"
				+" where ta.procinst_id = tb.procinst_id"
				+" and ta.state != 'active-join'"
				+" and tb.end > sysdate) t2,"
				+"t_business_businessrequest t3"
				+" where t2.procinst_id = t3.procinst_id"
				//+" and t2.curr_process_config_detail is not null"
				+" and t3.end > sysdate";
				if(StringUtils.isNotBlank(flowReqReportForm.getStartTime())){
					sql+=" and t3.begin>=to_date('"+flowReqReportForm.getStartTime()+"','yyyy-mm-dd')";
				}
				if(StringUtils.isNotBlank(flowReqReportForm.getEndTime())){
					sql+=" and t3.begin<=to_date('"+flowReqReportForm.getEndTime()+"','yyyy-mm-dd')";
				}	
			sql+=
				")"
				+" order by decode(short_name,'总计',1,0)";
		}
		//System.out.println(sql);
		list = getJdbcTemplate().query(sql,
				new RowMapper() {
					public Object mapRow(ResultSet rs, int arg1)
							throws SQLException {
						List<String> list = new ArrayList<String>();
						String s = "";
						for (int i = 1; i <= 4; i++) {
							s = rs.getString(i);
							list.add(s);
						}
						return list;
					}
				});
		return list;
	}
	@SuppressWarnings("unchecked")
	public List<List<String>> listSumBs(String sql, final int size,
			final int num) {

		List<List<String>> list = getJdbcTemplate().query(sql, new Object[] {},
				new RowMapper() {

					public Object mapRow(ResultSet rs, int arg1)
							throws SQLException {
						List<String> list = new ArrayList<String>();
						String s = "";
						for (int i = 0; i < size + num; i++) {// 2
							s = rs.getString(i + 1);
							list.add(s);
						}
						return list;
					}
				});
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<ReqDocu> findDocuList(Integer id) {
		String sql = "select t1.id_,t1.ref_entity_id_,t1.full_name_, t2.name fileTypeName,t3.name nodeName, t5.name createrUser, t1.begin_,t1.record_id,t2.id dictId,t1.content,t1.process_detail";
		sql += " from t_poc_req_docu t1, t_sys_dict t2,t_bpm_processconfigdetail t3,t_secu_user t4,t_organ_person t5";
		sql += " where t1.file_type_ = t2.id and t1.process_detail=t3.id(+) and t1.creator_=t4.user_id and t4.person=t5.id";
		sql += " and t1.PROCINST_ID=? order by to_number(t1.id_) asc";// t1.ref_entity_id_=?
		System.out.println("find docu list:" + sql.toString());
		List<ReqDocu> list = getJdbcTemplate().query(sql, new Object[] { id },
				new RowMapper() {

					public Object mapRow(ResultSet rs, int arg1)
							throws SQLException {
						// TODO Auto-generated method stub
						ProcessConfigDetail processDetail = new ProcessConfigDetail();
						ReqDocu reqDocu = new ReqDocu();
						reqDocu.setId(Integer.valueOf(rs.getString(1)));
						reqDocu.setRefEntityId(rs.getString(2));
						reqDocu.setFullName(rs.getString(3));
						Dict docType = new Dict();
						docType.setName(rs.getString(4));
						docType.setId(Integer.valueOf(rs.getString(9)));
						reqDocu.setDocType(docType);
						processDetail.setName(rs.getString(5));
						processDetail.setId(rs.getInt(11));
						reqDocu.setProcessDetail(processDetail);
						reqDocu.setCreator(rs.getString(6));
						reqDocu.setBegin(rs.getTimestamp(7));
						String recordId = rs.getString(8);
						if (StringUtils.isNotBlank(recordId)) {
							reqDocu.setRecordId(Integer.valueOf(recordId));
						}
						DocContent docContent=new DocContent();
						docContent.setId(rs.getInt(9));
						reqDocu.setContent(docContent);
						return reqDocu;
					}
				});
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<ReqDocu> findDocuListByRefIdAndType(Integer ref_entity_id,
			String ref_entity) {
		String sql = "select t1.id_,t1.ref_entity_id_,t1.full_name_, t2.name fileTypeName,t3.name nodeName, t5.name createrUser, t1.begin_,t1.record_id,t2.id dictId";
		sql += " from t_poc_req_docu t1, t_sys_dict t2,t_bpm_processconfigdetail t3,t_secu_user t4,t_organ_person t5";
		sql += " where t1.file_type_ = t2.id and t1.process_detail=t3.id(+) and t1.creator_=t4.user_id and t4.person=t5.id";
		sql += " and t1.ref_entity_id_=? and t1.ref_entity_=? order by to_number(t1.id_) asc";// t1.ref_entity_id_=?
		System.out.println("find docu list:" + sql.toString());
		List list = getJdbcTemplate().query(sql,
				new Object[] { ref_entity_id, ref_entity }, new RowMapper() {

					public Object mapRow(ResultSet rs, int arg1)
							throws SQLException {
						// TODO Auto-generated method stub
						ProcessConfigDetail processDetail = new ProcessConfigDetail();
						ReqDocu reqDocu = new ReqDocu();
						reqDocu.setId(Integer.valueOf(rs.getString(1)));
						reqDocu.setRefEntityId(rs.getString(2));
						reqDocu.setFullName(rs.getString(3));
						Dict docType = new Dict();
						docType.setName(rs.getString(4));
						docType.setId(Integer.valueOf(rs.getString(9)));
						reqDocu.setDocType(docType);
						processDetail.setName(rs.getString(5));
						reqDocu.setProcessDetail(processDetail);
						reqDocu.setCreator(rs.getString(6));
						reqDocu.setBegin(rs.getTimestamp(7));
						String recordId = rs.getString(8);
						if (StringUtils.isNotBlank(recordId)) {
							reqDocu.setRecordId(Integer.valueOf(recordId));
						}
						return reqDocu;
					}
				});
		return list;
	}

	public void deleteDocuByIds(String idArr[]) {
		String str = StringUtils.join(idArr, ",");
		String sql = "delete from t_poc_req_docu t where t.id_ in(" + str + ")";
		getJdbcTemplate().update(sql);
	}
	public void deleteDocContentByIds(String idArr[]){
		String str = StringUtils.join(idArr, ",");
		String sql = "delete from T_POC_REQ_DOC_CONTENT t where t.id_ in(" + str + ")";
		getJdbcTemplate().update(sql);
	}
	public void save(Object obj) {
		// TODO Auto-generated method stub
		getHibernateTemplate().saveOrUpdate(obj);
	}

	public BusinessRequest getBusinessRequestById(Integer id) {
		BusinessRequest businessRequest = super.get(id);
		return businessRequest;
	}

	@SuppressWarnings("unchecked")
	public BusinessRequest getBusinessRequest(Integer procinstId) {
		String hql = "select t from BusinessRequest t where t.procinstId=?";
		List list = getHibernateTemplate().find(hql, procinstId);
		if (list != null && list.size() > 0) {
			return (BusinessRequest) list.get(0);
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public ProdAdvReq getProdAdvReq(Integer procinstId) {
		String hql = "select t from ProdAdvReq t where t.procinstId="+procinstId;
		List list = getHibernateTemplate().find(hql);
		if (list != null && list.size() > 0) {
			return (ProdAdvReq) list.get(0);
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public ProdAdvReq getProdAdvReq(String childAreq, String opType) {
		String hql = "select t from ProdAdvReq t where t.childAreq=? and t.opType=?";
		List list = getHibernateTemplate().find(hql,
				new Object[] { childAreq, opType });
		if (list != null && list.size() > 0) {
			return (ProdAdvReq) list.get(0);
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public ProdAdvReq getProdAdvReqById(Integer id) {
		String hql = "select t from ProdAdvReq t where t.id=?";
		List list = getHibernateTemplate().find(hql, id);
		if (list != null && list.size() > 0) {
			return (ProdAdvReq) list.get(0);
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public ProdAdvReq getProdAdvReqByReqCode(String reqCode) {
		String hql = "select t from ProdAdvReq t where t.reqCode=?";
		List list = getHibernateTemplate().find(hql, reqCode);
		if (list != null && list.size() > 0) {
			return (ProdAdvReq) list.get(0);
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public Organization getOrgByPerson(Integer personId) {
		// TODO Auto-generated method stub
		String hql = "select t.organization from OrgRelaPerson t where t.person.id=?";
		List list = getHibernateTemplate().find(hql, personId);
		if (list != null && list.size() > 0) {
			return (Organization) list.get(0);
		}
		return null;
	}
	
	public Person getPersonByUserID(String userID){
		String sql="select t.user_id,t.person,t.name from v_secu_user t where t.user_id=?";
		Person person=(Person)getJdbcTemplate().queryForObject(sql, new Object[]{userID}, new RowMapper(){

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				Person person=new Person();
				person.setId(Integer.valueOf(rs.getString("person")));
				person.setName(rs.getString("name"));
				return person;
			}});
		return person;
	}
	
	public List findPersonByOrg(Integer orgId) {
		// TODO Auto-generated method stub
		String hql = "select t.person from OrgRelaPerson t where t.organization.id=? and t.person.name not like '%管理员%'";
		List list = getHibernateTemplate().find(hql, orgId);
		return list;
	}

	public List findPersonByOrg(Integer orgId,String duty) {
		// TODO Auto-generated method stub
		String hql = "select t.person from OrgRelaPerson t where t.organization.id=? and t.person.duty=?";
		List list = getHibernateTemplate().find(hql, new Object[]{orgId,duty});
		return list;
	}
	
	public List findPersonByOrg(Integer orgId,String dutyArr[]) {
		// TODO Auto-generated method stub
		String hql = "select t.person from OrgRelaPerson t where t.organization.id=? and t.person.duty in(";
		List paramList=new ArrayList();
		paramList.add(orgId);
		for (String obj : dutyArr) {
			hql+="?,";
			paramList.add(obj);
		}
		hql=hql.substring(0,hql.length()-1);
		hql+=")";
		List list = getHibernateTemplate().find(hql, paramList.toArray());
		return list;
	}
	
	public Person getPerson(String name) {

		String sql = "select distinct t.id from t_secu_role_rela_user a,t_secu_role b,t_secu_user d,t_organ_person t"
				+ " where a.secu_role=b.id"
				+ " and d.person=t.id"
				+ " and a.secu_user=d.id"
				+ " and b.name='产品经理角色'"
				+ " and t.name like '%" + name + "%'";

		int i = getJdbcTemplate().queryForInt(sql);
		System.out.println(i);
		return this.getPerson(i);
	}

	@SuppressWarnings("unchecked")
	public Person getPerson(Integer personId) {
		// TODO Auto-generated method stub
		String hql = "select t from Person t where t.id=?";
		List list = getHibernateTemplate().find(hql, personId);
		if (list != null && list.size() > 0) {
			return (Person) list.get(0);
		}
		return null;
	}

	public PageBean queryPageOaTaskList(FlowTaskForm flowTaskForm, int pageNo,
			int pageSize) {
		return queryPageTaskList(flowTaskForm, pageNo, pageSize);
	}

	/**
	 * t待办，已办，所有需求单
	 */
	@SuppressWarnings("unchecked")
	public PageBean queryPageTaskList(FlowTaskForm flowTaskForm, int pageNo,
			int pageSize) {
		StringBuilder sql = new StringBuilder();
		List paramList = new ArrayList();
		sql.append("select t.taksId,t.userId,t.req_code,t.titleName,t.startUserName,t.shortName,t.prodPerson,t.nodeName,");
		sql.append("t.userName,t.receive_time,t.transact_time,t.expire_time,t.nodeUrl,t.processUrl,");
		sql.append("t.procinst_id,t.reqContent,t.is_Send,t.state,t.business_tag");
		sql.append(" from v_flow_app_task t where 1=1");
		if (StringUtils.isNotBlank(flowTaskForm.getUserId())) {
			sql.append(" and t.userId=?");
			paramList.add(flowTaskForm.getUserId());
		}
		if (StringUtils.isNotBlank(flowTaskForm.getState())) {
			sql.append(" and t.state=?");
			paramList.add(flowTaskForm.getState());
		}

		if (StringUtils.isNotBlank(flowTaskForm.getReqCode())) {
			sql.append(" and t.req_code like ?");
			paramList.add(flowTaskForm.getReqCode() + "%");
		}
		if (StringUtils.isNotBlank(flowTaskForm.getReqTitle())) {
			sql.append(" and t.titleName like ?");
			paramList.add("%" + flowTaskForm.getReqTitle() + "%");
		}

		if (StringUtils.isNotBlank(flowTaskForm.getStartUser())) {
			sql.append(" and t.startUserName like ?");
			paramList.add("%" + flowTaskForm.getStartUser() + "%");
		}

		if (StringUtils.isNotBlank(flowTaskForm.getStartDept())) {
			sql.append(" and t.shortName = ?");
			paramList.add(flowTaskForm.getStartDept());
		}
		
		if (StringUtils.isNotBlank(flowTaskForm.getProdPerson())) {
			sql.append(" and t.prodPerson like ?");
			paramList.add("%"+flowTaskForm.getProdPerson()+"%");
		}
		
		if (StringUtils.isNotBlank(flowTaskForm.getStartTime())) {
			sql.append(" and t.receive_time >= to_date(?,'yyyy-mm-dd')");
			paramList.add(flowTaskForm.getStartTime());
		}
		if (StringUtils.isNotBlank(flowTaskForm.getEndTime())) {
			sql.append(" and t.receive_time <= to_date(?,'yyyy-mm-dd')");
			paramList.add(flowTaskForm.getEndTime());
		}
		if (StringUtils.isNotBlank(flowTaskForm.getBusinessTag())) {
			sql.append(" and t.business_tag like ?");
			paramList.add("%"+flowTaskForm.getBusinessTag().trim()+"%");
		}
		//sql.append(" order by t.req_code desc");
		
		sql.append(" order by nvl(to_number(t.procinst_id),0) desc");
		System.out.println("queryPageTaskList：" + sql.toString());
		PageBean pageBean = pagedQuerySQLJdbcTemplate(sql.toString(),
				new RowMapper() {

					public Object mapRow(ResultSet rs, int arg1)
							throws SQLException {
						// TODO Auto-generated method stub
						FlowTaskObjView flowTaskObjView = new FlowTaskObjView();
						flowTaskObjView.setFlowTaskId(rs.getString(1));
						flowTaskObjView.setUserId(rs.getString(2));
						flowTaskObjView.setReqCode(rs.getString(3));
						flowTaskObjView.setTitle(rs.getString(4));
						flowTaskObjView.setStartPerson(rs.getString(5));
						flowTaskObjView.setShortName(rs.getString(6));
						flowTaskObjView.setProdPerson(rs.getString(7));
						flowTaskObjView.setFlowName(rs.getString(8));
						flowTaskObjView.setUserName(rs.getString(9));
						flowTaskObjView.setStartTime(rs.getTimestamp(10));
						flowTaskObjView.setEndTime(rs.getTimestamp(11));
						flowTaskObjView.setExpireTime(rs.getTimestamp(12));

						String url = rs.getString(13);
						if (StringUtils.isBlank(url)) {
							url = rs.getString(14);
						}

						if (StringUtils.isBlank(url)) {
							url = "bpm/pvc/newBusinessReq_showDetail.action?q=1";
						}
						flowTaskObjView.setUrl(url);
						flowTaskObjView.setProcInstId(rs.getString(15));
						flowTaskObjView.setReqContent(rs.getString(16));
						flowTaskObjView.setIsSend(rs.getString(17));
						flowTaskObjView.setBusinessTag(rs.getString(19));
						return flowTaskObjView;
					}
				}, pageNo, pageSize, paramList.toArray());
		return pageBean;
	}
	/**
	 * 获取所有部门简称 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<String> getAllShortName(){
		List<String> list=new ArrayList<String>();
		//String sql="select distinct t.short_name from t_organ_organization t where t.short_name is not null";
		String sql="select t.name short_name from v_organ_short t order by t.org_order asc";
		list=getJdbcTemplate().query(sql, new RowMapper(){

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				String shortName=rs.getString("short_name");
				return shortName;
			}
			
		});
		return list;
	}
	
	@SuppressWarnings("unchecked")
	public PageBean queryPageAllBsReqList(FlowTaskForm flowTaskForm,
			int pageNo, int pageSize) {
		List paramList = new ArrayList();
		String sql = null;

		if (StringUtils.isNotBlank(flowTaskForm.getUserId())) {
			sql = "select t.reqid,t.reqcode,t.reqname,t.startpersonid,t.startpersonName,t.startdept,t.startdeptName,t.prodPerson,t.procinst_id,t.starttime,t.plantime,t.acttime,t.procinst_type,t.urltype,t.reqContent,t.business_tag,t.curruser,t.startdeptFullName"
					+ " from v_flow_app_bsreq t,"
					+ "(select distinct t2.procinst_id from t_bpm_proc_tran_record t2 where t2.transactor=?) pri"
					+ " where t.procinst_id=pri.procinst_id";
			paramList.add(flowTaskForm.getUserId());
		} else {
			sql = "select t.reqid,t.reqcode,t.reqname,t.startpersonid,t.startpersonName,t.startdept,t.startdeptName,t.prodPerson,t.procinst_id,t.starttime,t.plantime,t.acttime,t.procinst_type,t.urltype,t.reqContent,t.business_tag,t.curruser,t.startdeptFullName"
					+ " from v_flow_app_bsreq t where 1=1";
		}

		if (flowTaskForm != null) {
			if (StringUtils.isNotBlank(flowTaskForm.getReqCode())) {
				sql += " and t.reqcode like ?";
				paramList.add("%" + flowTaskForm.getReqCode().trim() + "%");
			}

			if (StringUtils.isNotBlank(flowTaskForm.getReqTitle())) {
				sql += " and t.reqname like ?";
				paramList.add("%" + flowTaskForm.getReqTitle().trim() + "%");
			}

			if (StringUtils.isNotBlank(flowTaskForm.getStartUser())) {
				sql += " and t.startpersonName like ?";
				paramList.add("%" + flowTaskForm.getStartUser().trim() + "%");
			}
			if (StringUtils.isNotBlank(flowTaskForm.getProdPerson())) {
				sql += " and t.prodPerson like ?";
				paramList.add("%" + flowTaskForm.getProdPerson().trim() + "%");
			}
			
			if (StringUtils.isNotBlank(flowTaskForm.getStartDept())) {
				sql += " and t.startdeptName = ?";
				paramList.add( flowTaskForm.getStartDept().trim());
			}
			/**
			if (StringUtils.isNotBlank(flowTaskForm.getStartDept())) {
				sql += " and t.startdeptFullName like ?";
				paramList.add('%'+flowTaskForm.getStartDept().trim()+"%");
			}
			**/
			if (StringUtils.isNotBlank(flowTaskForm.getStartTime())) {
				sql += " and t.starttime >= to_date(?,'yyyy-mm-dd')";
				paramList.add( flowTaskForm.getStartTime().trim());
			}
			if (StringUtils.isNotBlank(flowTaskForm.getEndTime())) {
				sql += " and t.starttime <= to_date(?,'yyyy-mm-dd')";
				paramList.add( flowTaskForm.getEndTime().trim());
			}
			if (StringUtils.isNotBlank(flowTaskForm.getBusinessTag())) {
				sql += " and t.business_tag like ?";
				paramList.add("%" + flowTaskForm.getBusinessTag().trim() + "%");
			}
			if (StringUtils.isNotBlank(flowTaskForm.getState())) {
				sql += " and t.state is not null";
			}
		}
		//sql += " order by t.reqcode desc";
		 sql+=" order by nvl(to_number(t.procinst_id),0) desc";

		System.out.println("sql：" + sql);
		PageBean pageBean = pagedQuerySQLJdbcTemplate(sql, new RowMapper() {

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				BusinessRequest businessRequest = new BusinessRequest();
				String id = rs.getString(1);
				businessRequest.setId(Integer.valueOf(id));
				businessRequest.setReqCode(rs.getString(2));
				businessRequest.setName(rs.getString(3));
				businessRequest.setStartPerson(rs.getString(5));
				businessRequest.setStartDept(rs.getString(7));
				Person person=new Person();
				person.setName(rs.getString(8));
				businessRequest.setProdPerson(person);
				String processInstIdS = rs.getString(9);
				if (StringUtils.isNotBlank(processInstIdS)) {
					businessRequest.setProcinstId(Integer
							.valueOf(processInstIdS));
				}

				businessRequest.setBegin(rs.getTimestamp(10));
				businessRequest.setPlanTime(rs.getTimestamp(11));
				businessRequest.setActTime(rs.getTimestamp(12));

				businessRequest.setProcinstType(rs.getString(13));
				businessRequest.setUrlType(rs.getString(14));
				businessRequest.setContent(rs.getString(15));
				businessRequest.setBusinessTag(rs.getString(16));
				businessRequest.setCurrUser(rs.getString(17));
				return businessRequest;
			}
		}, pageNo, pageSize, paramList.toArray());

		return pageBean;
	}

	@SuppressWarnings("unchecked")
	public PageBean queryPageBsReqList(FlowTaskForm flowTaskForm, int pageNo,
			int pageSize) {
		List paramList = new ArrayList();
		String sql = null;

		if (StringUtils.isNotBlank(flowTaskForm.getUserId())) {
			sql = "select t.id_,t.req_code,t.name_,t.start_person,t.begin,t.procinst_id,t.start_time,t.start_dept,t.procinst_type,t.act_time,t.plan_time"
					+ " from t_business_businessrequest t,"
					+ "(select distinct t2.procinst_id from t_bpm_proc_tran_record t2 where t2.transactor=?) pri"
					+ " where t.procinst_id=pri.procinst_id";
			paramList.add(flowTaskForm.getUserId());
		} else {
			sql = "select t.id_,t.req_code,t.name_,t.start_person,t.begin,t.procinst_id,t.start_time,t.start_dept,t.procinst_type,t.act_time,t.plan_time"
					+ " from t_business_businessrequest t where 1=1";
		}

		if (flowTaskForm != null) {
			if (StringUtils.isNotBlank(flowTaskForm.getReqCode())) {
				sql += " and t.req_code like ?";
				paramList.add("%" + flowTaskForm.getReqCode().trim() + "%");
			}

			if (StringUtils.isNotBlank(flowTaskForm.getReqTitle())) {
				sql += " and t.name_ like ?";
				paramList.add("%" + flowTaskForm.getReqTitle().trim() + "%");
			}

			if (StringUtils.isNotBlank(flowTaskForm.getStartUser())) {
				sql += " and t.start_person like ?";
				paramList.add("%" + flowTaskForm.getStartUser().trim() + "%");
			}

			if (StringUtils.isNotBlank(flowTaskForm.getProcinstType())) {
				sql += " and t.procinst_type = ?";
				paramList.add(flowTaskForm.getProcinstType());
			}
		}
		sql += " order by to_number(t.id_) desc";
		System.out.println("sql：" + sql);
		PageBean pageBean = pagedQuerySQLJdbcTemplate(sql, new RowMapper() {

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				BusinessRequest businessRequest = new BusinessRequest();
				businessRequest.setReqCode(rs.getString(2));

				businessRequest.setName(rs.getString(3));
				businessRequest.setStartPerson(rs.getString(4));
				businessRequest.setBegin(rs.getTimestamp(5));
				businessRequest.setAdvanTime(rs.getDate(7));
				String processInstIdS = rs.getString(6);
				if (StringUtils.isNotBlank(processInstIdS)) {
					businessRequest.setProcinstId(Integer
							.valueOf(processInstIdS));
				}
				businessRequest.setStartDept(rs.getString(8));

				businessRequest.setProcinstType(rs.getString(9));
				businessRequest.setActTime(rs.getDate(10));
				businessRequest.setPlanTime(rs.getDate(11));
				return businessRequest;
			}
		}, pageNo, pageSize, paramList.toArray());

		return pageBean;
	}

	@SuppressWarnings("unchecked")
	public PageBean queryPageProdAdvReqList(FlowTaskForm flowTaskForm,
			int pageNo, int pageSize) {
		List paramList = new ArrayList();
		String sql = null;

		if (StringUtils.isNotBlank(flowTaskForm.getUserId())) {
			// sql="select t.id_,t.req_code_,t.name_,t2.name
			// startUser,t.start_time_,t.procinst_id,t.advan_time_,t3.name
			// startDept from t_poc_req_adv_list t,(select distinct
			// t2.procinst_id from t_bpm_proc_tran_record t2 where
			// t2.transactor=?) pri,t_organ_person t2,t_organ_organization t3" +
			// " where t.procinst_id=pri.procinst_id and t.start_user_=t2.id and
			// t.start_dept_=t3.id";

			sql = "select t.id_,t.req_code_,t.name_,t2.name startUser,t.start_time_,t.procinst_id,t.advan_time_,t3.name startDept,t.rela_req_,t.child_areq_ from t_poc_req_adv_list t,t_organ_person t2,t_organ_organization t3"
					+ " where t.start_user_=t2.id and t.start_dept_=t3.id and t.start_user_=? and t.end_>sysdate";
			paramList.add(flowTaskForm.getUserId());
		} else {
			sql = "select t.id_,t.req_code_,t.name_,t2.name startUser,t.start_time_,t.procinst_id,t.advan_time_,t3.name startDept,t.rela_req_,t.child_areq_ from t_poc_req_adv_list t,t_organ_person t2,t_organ_organization t3"
					+ " where t.start_user_=t2.id and t.start_dept_=t3.id and t.end_>sysdate";
		}

		if (flowTaskForm != null) {
			if (StringUtils.isNotBlank(flowTaskForm.getReqCode())) {
				sql += " and t.req_code_ like ?";
				paramList.add("%" + flowTaskForm.getReqCode().trim() + "%");
			}

			if (StringUtils.isNotBlank(flowTaskForm.getReqTitle())) {
				sql += " and t.name_ like ?";
				paramList.add("%" + flowTaskForm.getReqTitle().trim() + "%");
			}

			if (StringUtils.isNotBlank(flowTaskForm.getState())) {
				sql += " and t.tran_status_ in (" + flowTaskForm.getState()
						+ ")";
				// paramList.add(flowTaskForm.getState());
			}
			if (StringUtils.isNotBlank(flowTaskForm.getStartUser())) {
				sql += " and t2.name like ?";
				paramList.add("%" + flowTaskForm.getStartUser().trim() + "%");
			}
		}
		sql += " order by to_number(t.id_) desc";
		System.out.println("queryPageProdAdvReqList:" + sql);
		PageBean pageBean = pagedQuerySQLJdbcTemplate(sql, new RowMapper() {

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ProdAdvReq prodAdvReq = new ProdAdvReq();
				prodAdvReq.setId(Integer.valueOf(rs.getString(1)));
				prodAdvReq.setReqCode(rs.getString(2));

				prodAdvReq.setName(rs.getString(3));
				prodAdvReq.setStartUserName(rs.getString(4));
				prodAdvReq.setBegin(rs.getTimestamp(5));
				prodAdvReq.setAdvanTime(rs.getDate(7));

				String processInstIdS = rs.getString(6);
				if (StringUtils.isNotBlank(processInstIdS)) {
					prodAdvReq.setProcinstId(Integer.valueOf(processInstIdS));
				}
				Organization organization = new Organization();
				organization.setName(rs.getString(8));
				prodAdvReq.setStartDept(organization);
				String realReqId = rs.getString(9);
				prodAdvReq.setChildAreq(realReqId);
				return prodAdvReq;
			}
		}, pageNo, pageSize, paramList.toArray());

		return pageBean;
	}

	public void deleteDateControlByReqId(String reqId){
		String sql = "delete from t_bpm_date_manager t where t.req_id=" + reqId;
		getJdbcTemplate().update(sql);
	}
	public void delete(Object object){
		this.getHibernateTemplate().delete(object);
	}
	
	@SuppressWarnings("unchecked")
	public List<DateControl> findDateContolByReqId(String reqId){
		List<DateControl> list=new ArrayList<DateControl>();
		String hql=" from DateControl t where t.reqId="+reqId;
		list=getHibernateTemplate().find(hql);
		return list;
	}
	
	@SuppressWarnings("unchecked")
	public List<DateManager> dateList() {
		List<DateManager> list = new ArrayList<DateManager>();
		String sql = "select  b.procinst_id,t.processconfigdetail,d.name,to_char(t.manager_date, 'yyyy-mm-dd') as manager_date,a.end_date"
			+" from t_bpm_date_manager t"
			+" ,t_bpm_processconfigdetail d"
			+" ,t_business_businessrequest b"
			+" ,("
			+"   select b.id_,t.process_step,to_char(t.transact_time, 'yyyy-mm-dd') as end_date"
			+"  from t_Bpm_Proc_Tran_Record t"
			+"  ,t_business_businessrequest b"
			+"  where b.procinst_id=t.procinst_id"
			+"  and t.orderby is null"
			+"  and t.transact_time is not null"
			+" ) a"
			+"  where d.id=t.processconfigdetail"
			+" and b.id_=t.req_id"
			+"  and t.req_id=a.id_(+)"
			+"  and t.processconfigdetail=a.process_step(+)" 
			+"  order by b.procinst_id, d.order_by";
		System.out.println(sql);
		list = getJdbcTemplate().query(sql, new RowMapper() {

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				DateManager dateManager = new DateManager();
				dateManager.setProcinstId(rs.getInt(1));
				dateManager.setProcessconfigdetail(rs.getInt(2));
				dateManager.setProcessName(rs.getString(3));
				dateManager.setManagerName(rs.getString(4));
				dateManager.setEndDate(rs.getString(5));
				return dateManager;
			}
		});
		return list;
	}
	public Report getReportById(Integer id){
		return (Report) this.getHibernateTemplate().get(Report.class, id);
	}
	@SuppressWarnings("unchecked")
	public Report getLastReport(String reqId,String type){
		String sql="";
		if("01".equals(type)){
			sql="select t.id,t.person_id,t.operate_time,t.operate_info,t.help_item,t.next_work from t_bpm_report t where to_char(t.operate_time,'IW')=to_char(sysdate,'IW') and t.req_id='"+reqId+"' and type ='"+type+"'";
		}else {
			sql="select t.id,t.person_id,t.operate_time,t.operate_info,t.help_item,t.next_work from t_bpm_report t where to_char(t.operate_time,'yyyy-mm')=to_char(sysdate,'yyyy-mm') and t.req_id='"+reqId+"' and type ='"+type+"'";
		}
		List<Report> list=new ArrayList<Report>();
		list=getJdbcTemplate().query(sql, new RowMapper() {
			
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				Report report=new Report();
				report.setId(rs.getInt(1));
				
				report.setPersonId(rs.getString(2));
				report.setOperateTime(rs.getDate(3));
				report.setOperateInfo(rs.getString(4));
				report.setHelpItem(rs.getString(5));
				report.setNextWork(rs.getString(6));
				return report;
			}
		});
		if(list.size()!=0){
			return list.get(0);
		}else {
			return null;
		}
	}
	public int weekNum(){
		String sql="select to_number(to_char(sysdate,'FMWW')) from dual";
		return this.getJdbcTemplate().queryForInt(sql);
	}
	@SuppressWarnings("unchecked")
	public List<Report> reportList(String reqId,String type){
		String sql="select t.id,t.person_id,t.operate_time,t.operate_info,t.help_item,t.next_work from t_bpm_report t where t.req_id='"+reqId+"' and t.type='"+type+"'";
		List<Report> list=new ArrayList<Report>();
		list=getJdbcTemplate().query(sql, new RowMapper() {
			
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				Report report=new Report();
				report.setId(rs.getInt(1));
				report.setPersonId(rs.getString(2));
				report.setOperateTime(rs.getDate(3));
				report.setOperateInfo(rs.getString(4));
				report.setHelpItem(rs.getString(5));
				report.setNextWork(rs.getString(6));
				return report;
			}
		});
		return list;
	}
	@SuppressWarnings("unchecked")
	public PageBean queryPageBsReqReportList(
			FlowReqReportForm flowReqReportForm, int pageNo, int pageSize) {
		List paramList = new ArrayList();
		String sql = "select t1.id_,t1.req_code,t1.name_,t1.importance,t1.type,t1.start_person,t5.short_name start_dept,t2.name prodName,t3.full_Name_ prodDeptName,t1.start_time,t1.procinst_id,t1.content,t1.begin,t1.plan_time"
				+ " from t_business_businessrequest t1,t_organ_person t2,t_organ_organization t3,t_bpm_transactprocessstatus t4,t_organ_organization t5"
				+ " where t1.prod_person=t2.id and t1.prod_dept=t3.id and t1.start_dept_id=t5.id and t1.procinst_id=t4.id and t1.end>sysdate";
		if (flowReqReportForm != null) {
			if (StringUtils.isNotBlank(flowReqReportForm.getReqTitle())) {
				sql += " and t1.name_ like ?";
				paramList.add("%" + flowReqReportForm.getReqTitle().trim()
						+ "%");
			}

			if (StringUtils.isNotBlank(flowReqReportForm.getProcessId())) {
				sql += " and t4.curr_process_config=?";
				paramList.add(flowReqReportForm.getProcessId());
			}
			if (StringUtils.isNotBlank(flowReqReportForm.getDaiType())) {
				sql += " and t1.dai_type=?";
				paramList.add(flowReqReportForm.getDaiType());
			}
			if (StringUtils.isNotBlank(flowReqReportForm.getProdPerson())) {
				sql += " and t2.name like ?";
				paramList.add("%"+flowReqReportForm.getProdPerson()+"%");
			}
			if (StringUtils.isNotBlank(flowReqReportForm.getStartTime())) {
				sql += " and to_char(t1.begin,'yyyy-mm-dd') >= ?";
				paramList.add(flowReqReportForm.getStartTime());
			}
			if (StringUtils.isNotBlank(flowReqReportForm.getEndTime())) {
				sql += " and to_char(t1.begin,'yyyy-mm-dd') <= ?";
				paramList.add(flowReqReportForm.getEndTime());
			}
			if (StringUtils.isNotBlank(flowReqReportForm.getShortDept())) {
				sql += " and t5.short_name = ?";
				paramList.add(flowReqReportForm.getShortDept());
			}
			if (StringUtils.isNotBlank(flowReqReportForm.getBusinessTag())) {
				sql += " and t1.business_tag like ?";
				paramList.add("%"+flowReqReportForm.getBusinessTag()+"%");
			}
		}
		if (StringUtils.isNotBlank(flowReqReportForm.getOrderByName())) {
			sql += " order by " + flowReqReportForm.getOrderByName() + " desc";
		} else {
			sql += " order by t1.req_code desc";
		}
		System.out.println(sql);
		PageBean pageBean = pagedQuerySQLJdbcTemplate(sql, new RowMapper() {

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				BusinessRequest businessRequest = new BusinessRequest();
				businessRequest.setReqCode(rs.getString(2));
				businessRequest.setName(rs.getString(3));
				businessRequest.setImportance(rs.getString(4));
				businessRequest.setType(rs.getString(5));

				businessRequest.setStartPerson(rs.getString(6));
				businessRequest.setStartDept(rs.getString(7));
				Person person = new Person();
				person.setName(rs.getString(8));
				businessRequest.setProdPerson(person);
				Person startPerson = new Person();
				startPerson.setName(rs.getString(6));
				businessRequest.setStartPersonObj(startPerson);

				Organization prodDept = new Organization();
				prodDept.setName(rs.getString(9));
				businessRequest.setProdDept(prodDept);
				Organization startProdDept = new Organization();
				startProdDept.setName(rs.getString(7));
				businessRequest.setStartDeptObj(startProdDept);

				businessRequest.setAdvanTime(rs.getDate(10));
				String processInstIdS = rs.getString(11);
				if (StringUtils.isNotBlank(processInstIdS)) {
					businessRequest.setProcinstId(Integer
							.valueOf(processInstIdS));
				}
				businessRequest.setContent(rs.getString(12));
				businessRequest.setBegin(rs.getDate(13));
				businessRequest.setPlanTime(rs.getDate(14));
				return businessRequest;
			}
		}, pageNo, pageSize, paramList.toArray());

		return pageBean;
	}

	@SuppressWarnings("unchecked")
	public PageBean queryPageWeekReportList(
			FlowReqReportForm flowReqReportForm, int pageNo, int pageSize) {
		List paramList = new ArrayList();
		String sql = "select t1.id_,t1.req_code,t1.name_,t1.importance,t1.type,t1.start_person,t5.full_Name_ start_dept,t2.name prodName,"
				+ "t3.full_Name_ prodDeptName,t1.start_time,t1.procinst_id,t1.content,t1.begin,t1.plan_time,t7.operate_info as week_content"
				+ " from t_business_businessrequest t1,t_organ_person t2,t_organ_organization t3,t_bpm_transactprocessstatus t4,t_organ_organization t5, ("
				+ " select t.person_id,t.type,t.req_id,max(t.operate_time) as end_time from t_bpm_report t group by t.person_id,t.type,t.req_id"
				+ ")t6,t_bpm_report t7"
				+ " where t1.id_=t6.req_id and t1.prod_person = t2.id and t7.operate_time=t6.end_time and t1.prod_dept=t3.id and t1.start_dept_id=t5.id and t1.procinst_id=t4.id and t1.end>sysdate";
		if (flowReqReportForm != null) {
			if (StringUtils.isNotBlank(flowReqReportForm.getReqTitle())) {
				sql += " and t1.name_ like ?";
				paramList.add("%" + flowReqReportForm.getReqTitle().trim()
						+ "%");
			}

			if (StringUtils.isNotBlank(flowReqReportForm.getProcessId())) {
				sql += " and t4.curr_process_config=?";
				paramList.add(flowReqReportForm.getProcessId());
			}
			if (StringUtils.isNotBlank(flowReqReportForm.getType())) {
				sql += " and t7.type=?";
				paramList.add(flowReqReportForm.getType());
			}
			if (StringUtils.isNotBlank(flowReqReportForm.getDaiType())) {
				sql += " and t1.dai_type=?";
				paramList.add(flowReqReportForm.getDaiType());
			}
		}
		if (StringUtils.isNotBlank(flowReqReportForm.getOrderByName())) {
			sql += " order by " + flowReqReportForm.getOrderByName() + " desc";
		} else {
			sql += " order by to_number(t1.id_) desc";
		}
		System.out.println(sql);
		PageBean pageBean = pagedQuerySQLJdbcTemplate(sql, new RowMapper() {

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				BusinessRequest businessRequest = new BusinessRequest();
				businessRequest.setReqCode(rs.getString(2));
				businessRequest.setName(rs.getString(3));
				businessRequest.setImportance(rs.getString(4));
				businessRequest.setType(rs.getString(5));

				businessRequest.setStartPerson(rs.getString(6));
				businessRequest.setStartDept(rs.getString(7));
				Person person = new Person();
				person.setName(rs.getString(8));
				businessRequest.setProdPerson(person);
				Person startPerson = new Person();
				startPerson.setName(rs.getString(6));
				businessRequest.setStartPersonObj(startPerson);

				Organization prodDept = new Organization();
				prodDept.setName(rs.getString(9));
				businessRequest.setProdDept(prodDept);
				Organization startProdDept = new Organization();
				startProdDept.setName(rs.getString(7));
				businessRequest.setStartDeptObj(startProdDept);

				businessRequest.setAdvanTime(rs.getDate(10));
				String processInstIdS = rs.getString(11);
				if (StringUtils.isNotBlank(processInstIdS)) {
					businessRequest.setProcinstId(Integer
							.valueOf(processInstIdS));
				}
				businessRequest.setContent(rs.getString(12));
				businessRequest.setBegin(rs.getDate(13));
				businessRequest.setPlanTime(rs.getDate(14));
				businessRequest.setWeekContent(rs.getString(15));
				return businessRequest;
			}
		}, pageNo, pageSize, paramList.toArray());

		return pageBean;
	}

	@SuppressWarnings("unchecked")
	public PageBean queryPageNeedWeek(FlowReqReportForm flowReqReportForm,User user,String type, int pageNo,int pageSize) {
		List paramList = new ArrayList();
		String sql = "select t1.id_,t1.req_code,t1.name_,t1.start_person,t5.full_Name_ start_dept,t2.id person_id,t1.procinst_id,t1.content,t1.begin "
				+ " from t_business_businessrequest t1,t_organ_person t2,t_bpm_transactprocessstatus t4,t_organ_organization t5"
				+ "  where t1.prod_person = t2.id and t1.start_dept_id = t5.id and t1.procinst_id = t4.id and t1.end > sysdate ";
		if(flowReqReportForm!=null){
			if(flowReqReportForm.getReqTitle().trim()!=null&&!"".equals(flowReqReportForm.getReqTitle().trim())){
				sql+=" and t1.name_ like '%"+flowReqReportForm.getReqTitle().trim()+"%'" ;
			}
			if(flowReqReportForm.getState()!=null&&!"".equals(flowReqReportForm.getState())){
				sql+=" and t4.end_time "+flowReqReportForm.getState() ;
			}
		}
		if(user!=null){
			sql+=" and t2.id="+user.getPersonID();
			if("01".equals(type)){
				sql+=" and t1.week_state='01'";
			}else{
				sql+=" and t1.month_state='01'";
			}
		}
		
		sql += " order by to_number(t1.id_) desc";

		System.out.println(sql);
		PageBean pageBean = pagedQuerySQLJdbcTemplate(sql, new RowMapper() {

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				BusinessRequest businessRequest = new BusinessRequest();
				businessRequest.setId(rs.getInt(1));
				businessRequest.setReqCode(rs.getString(2));
				businessRequest.setName(rs.getString(3));
				businessRequest.setStartPerson(rs.getString(4));
				businessRequest.setStartDept(rs.getString(5));
				Person person = new Person();
				person.setId(rs.getInt(6));
				businessRequest.setProdPerson(person);
				businessRequest.setProcinstId(rs.getInt(7));
				businessRequest.setContent(rs.getString(8));
				businessRequest.setBegin(rs.getDate(9));
				return businessRequest;
			}
		}, pageNo, pageSize, paramList.toArray());

		return pageBean;
	}

	@SuppressWarnings("unchecked")
	public PageBean queryPageBsReqReportList(User user, String reqName,
			int pageNo, int pageSize) {
		List paramList = new ArrayList();
		String sql = "select a.req_code,a.id_,a.name_,a.procinst_id,c.state "
				+ " from t_business_businessrequest a,(select distinct b.req_id from t_bpm_date_manager b) b,t_bpm_proc_tran_record c "
				+ " where a.id_=b.req_id "
				+ " and a.procinst_id=c.procinst_id " + " and c.state='active'"
		// + " and a.prod_person='"+user.getPersonID()+"'"
		;
		if (reqName != null && !"".equals(reqName)) {
			sql += " and a.name_ like '%" + reqName.trim() + "%'";
		}
		System.out.println(sql);
		PageBean pageBean =  pagedQuerySQLJdbcTemplate(sql.toString(), new RowMapper() {

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				BusinessRequest businessRequest = new BusinessRequest();
				businessRequest.setReqCode(rs.getString(1));
				businessRequest.setId(rs.getInt(2));
				businessRequest.setName(rs.getString(3));
				businessRequest.setProcinstId(rs.getInt(4));
				return businessRequest;
			}
		}, pageNo, pageSize, paramList.toArray());
	
		return pageBean;
	}

	@SuppressWarnings("unchecked")
	public List<ReqProgress> findReqProgressList(Integer reqId) {
		String hql = "select t from ReqProgress t where t.requestBusiness.id=? order by t.id asc";
		return getHibernateTemplate().find(hql, reqId);
	}

	@SuppressWarnings("unchecked")
	public List<ReqProgress> findReqProgressListByTaskIds(String taskIds) {
		String hql = "select t from ReqProgress t where t.recotdId in("
				+ taskIds + ")";
		return getHibernateTemplate().find(hql);
	}

	@SuppressWarnings("unchecked")
	public List<ReqDelay> findReqReqDelayList(Integer reqId) {
		String hql = "select t from ReqDelay t where t.requestBusiness.id=? order by t.id asc";
		return getHibernateTemplate().find(hql, reqId);
	}

	@SuppressWarnings("unchecked")
	public void tranferDataRefuse() {

		String hql = "select t from ProdAdvReq t where t.tranStatus='REFUSE' and t.procinstId is null";
		List prodList = getHibernateTemplate().find(hql);
		ProcessConfig processConfig = new ProcessConfig();
		processConfig.setId(Integer.valueOf("102"));
		ProcessConfigDetail endConfigDetail = new ProcessConfigDetail();
		endConfigDetail.setId(Integer.valueOf("149"));

		ProcessConfigDetail startNode = new ProcessConfigDetail();
		startNode.setId(Integer.valueOf("150"));

		ProcessConfigDetail resPonseNode = new ProcessConfigDetail();
		resPonseNode.setId(Integer.valueOf("151"));

		Person person = new Person();
		person.setId(Integer.valueOf("517"));
		String str = null;
		for (Object object : prodList) {

			ProdAdvReq prodAdvReq = (ProdAdvReq) object;
			String tranReason = prodAdvReq.getTranReason();
			if (StringUtils.isBlank(tranReason)) {
				continue;
			}
			System.out.println("fdsa");
			String tranReqsonArr[] = StringUtils.splitByWholeSeparator(
					tranReason, "<BR>");
			if ("FJ1003160482".equals(prodAdvReq.getReqCode())) {
				TransactProcessStatus processStatus = new TransactProcessStatus();
				processStatus.setBegin(prodAdvReq.getBegin());
				processStatus.setCurrProcessConfig(processConfig);
				processStatus.setCurrProcessConfigDetail(endConfigDetail);
				processStatus.setState(ProcessPvcService.ACTIVE_ROOT);
				save(processStatus);
				prodAdvReq.setProcinstId(processStatus.getId());
				save(prodAdvReq);
				processStatus.setProcinstId(processStatus.getId());

				// 1
				String dateStr = tranReqsonArr[0].substring(0, 16);
				Date date = null;
				Date date2 = null;
				try {
					date = DateUtil.parse(dateStr, "yyyy-MM-dd HH:mm");
				} catch (ParseException e) {
					e.printStackTrace();
				}
				date2 = date;
				ProcTranRecord procTranRecord = new ProcTranRecord();
				procTranRecord.setTransactor(prodAdvReq.getStartPerson());
				procTranRecord.setReceiveTime(date);
				procTranRecord.setTransactTime(date);

				procTranRecord.setTransactInfo("");
				procTranRecord.setProcessConfig(processConfig);
				procTranRecord.setProcessStep(startNode);
				procTranRecord.setProcinstId(processStatus.getId());
				procTranRecord.setState(TaskService.TASK_STATE_END);
				procTranRecord.setTaskType(TaskService.TASK_TYPE[0][0]);
				save(procTranRecord);

				// 2
				dateStr = tranReqsonArr[1].substring(0, 16);
				str = StringUtils.substringAfter(tranReqsonArr[1], "办理意见：");
				date = date2;
				date2 = null;
				try {
					date2 = DateUtil.parse(dateStr, "yyyy-MM-dd HH:mm");
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				procTranRecord = new ProcTranRecord();
				procTranRecord.setTransactor(person);
				procTranRecord.setReceiveTime(date);
				procTranRecord.setTransactTime(date2);

				procTranRecord.setTransactInfo(str);
				procTranRecord.setProcessConfig(processConfig);
				procTranRecord.setProcessStep(resPonseNode);
				procTranRecord.setProcinstId(processStatus.getId());
				procTranRecord.setState(TaskService.TASK_STATE_END);
				procTranRecord.setTaskType(TaskService.TASK_TYPE[0][0]);
				save(procTranRecord);

				// 3

				dateStr = tranReqsonArr[3].substring(0, 16);
				str = StringUtils.substringAfter(tranReqsonArr[3], "办理意见：");
				date = date2;
				date2 = null;
				try {
					date2 = DateUtil.parse(dateStr, "yyyy-MM-dd HH:mm");
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				procTranRecord = new ProcTranRecord();
				procTranRecord.setTransactor(prodAdvReq.getStartPerson());
				procTranRecord.setReceiveTime(date);
				procTranRecord.setTransactTime(date2);

				procTranRecord.setTransactInfo(str);
				procTranRecord.setProcessConfig(processConfig);
				procTranRecord.setProcessStep(startNode);
				procTranRecord.setProcinstId(processStatus.getId());
				procTranRecord.setState(TaskService.TASK_STATE_END);
				procTranRecord.setTaskType(TaskService.TASK_TYPE[0][0]);
				save(procTranRecord);

				// 4
				dateStr = tranReqsonArr[4].substring(0, 16);
				str = StringUtils.substringAfter(tranReqsonArr[4], "办理意见：");
				date = date2;
				date2 = null;
				try {
					date2 = DateUtil.parse(dateStr, "yyyy-MM-dd HH:mm");
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				procTranRecord = new ProcTranRecord();
				procTranRecord.setTransactor(person);
				procTranRecord.setReceiveTime(date);
				procTranRecord.setTransactTime(date2);

				procTranRecord.setTransactInfo(str);
				procTranRecord.setProcessConfig(processConfig);
				procTranRecord.setProcessStep(resPonseNode);
				procTranRecord.setProcinstId(processStatus.getId());
				procTranRecord.setState(TaskService.TASK_STATE_END);
				procTranRecord.setTaskType(TaskService.TASK_TYPE[0][0]);
				save(procTranRecord);

				processStatus.setActId(procTranRecord.getId());
				processStatus.setEnd(date2);
				save(processStatus);
			} else if ("FJ1004151122".equals(prodAdvReq.getReqCode())) {

			} else if ("FJ1004231263".equals(prodAdvReq.getReqCode())) {

			} else {
				continue;
			}

		}
	}

	@SuppressWarnings("unchecked")
	public void tranferData() {
		// String hql="select t from ProdAdvReq t where t.relaReq is not null
		// and t.procinstId is null";
		String hql = "select t from ProdAdvReq t where t.tranStatus='PROD' and t.procinstId is null";
		List prodList = getHibernateTemplate().find(hql);
		ProcessConfig processConfig = new ProcessConfig();
		processConfig.setId(Integer.valueOf("102"));
		ProcessConfigDetail endConfigDetail = new ProcessConfigDetail();
		endConfigDetail.setId(Integer.valueOf("149"));

		ProcessConfigDetail startNode = new ProcessConfigDetail();
		startNode.setId(Integer.valueOf("150"));

		ProcessConfigDetail resPonseNode = new ProcessConfigDetail();
		resPonseNode.setId(Integer.valueOf("151"));

		Person person = new Person();
		person.setId(Integer.valueOf("517"));
		String str = null;
		for (Object object : prodList) {

			ProdAdvReq prodAdvReq = (ProdAdvReq) object;
			String tranReason = prodAdvReq.getTranReason();
			if (StringUtils.isBlank(tranReason)) {
				continue;
			}

			String tranReqsonArr[] = StringUtils.splitByWholeSeparator(
					tranReason, "<BR>");

			if ("FJ1003160482".equals(prodAdvReq.getReqCode())) {
				TransactProcessStatus processStatus = new TransactProcessStatus();
				processStatus.setBegin(prodAdvReq.getBegin());
				processStatus.setCurrProcessConfig(processConfig);
				processStatus.setCurrProcessConfigDetail(endConfigDetail);
				processStatus.setState(ProcessPvcService.ACTIVE_ROOT);
				save(processStatus);
				prodAdvReq.setProcinstId(processStatus.getId());
				save(prodAdvReq);
				processStatus.setProcinstId(processStatus.getId());

				// 1
				String dateStr = tranReqsonArr[0].substring(0, 16);
				Date date = null;
				Date date2 = null;
				try {
					date = DateUtil.parse(dateStr, "yyyy-MM-dd HH:mm");
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				date2 = date;
				ProcTranRecord procTranRecord = new ProcTranRecord();
				procTranRecord.setTransactor(prodAdvReq.getStartPerson());
				procTranRecord.setReceiveTime(date);
				procTranRecord.setTransactTime(date);

				procTranRecord.setTransactInfo("");
				procTranRecord.setProcessConfig(processConfig);
				procTranRecord.setProcessStep(startNode);
				procTranRecord.setProcinstId(processStatus.getId());
				procTranRecord.setState(TaskService.TASK_STATE_END);
				procTranRecord.setTaskType(TaskService.TASK_TYPE[0][0]);
				save(procTranRecord);

				// 2
				dateStr = tranReqsonArr[1].substring(0, 16);
				str = StringUtils.substringAfter(tranReqsonArr[1], "办理意见：");
				date = date2;
				date2 = null;
				try {
					date2 = DateUtil.parse(dateStr, "yyyy-MM-dd HH:mm");
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				procTranRecord = new ProcTranRecord();
				procTranRecord.setTransactor(person);
				procTranRecord.setReceiveTime(date);
				procTranRecord.setTransactTime(date2);

				procTranRecord.setTransactInfo(str);
				procTranRecord.setProcessConfig(processConfig);
				procTranRecord.setProcessStep(resPonseNode);
				procTranRecord.setProcinstId(processStatus.getId());
				procTranRecord.setState(TaskService.TASK_STATE_END);
				procTranRecord.setTaskType(TaskService.TASK_TYPE[0][0]);
				save(procTranRecord);

				// 3

				// 5
				dateStr = tranReqsonArr[2].substring(0, 16);
				str = StringUtils.substringAfter(tranReqsonArr[2], "办理意见：");
				date = date2;
				date2 = null;
				try {
					date2 = DateUtil.parse(dateStr, "yyyy-MM-dd HH:mm");
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				procTranRecord = new ProcTranRecord();
				procTranRecord.setTransactor(prodAdvReq.getStartPerson());
				procTranRecord.setReceiveTime(date);
				procTranRecord.setTransactTime(date2);

				procTranRecord.setTransactInfo(str);
				procTranRecord.setProcessConfig(processConfig);
				procTranRecord.setProcessStep(startNode);
				procTranRecord.setProcinstId(processStatus.getId());
				procTranRecord.setState(TaskService.TASK_STATE_END);
				procTranRecord.setTaskType(TaskService.TASK_TYPE[0][0]);
				save(procTranRecord);

				// 6
				dateStr = tranReqsonArr[3].substring(0, 16);
				str = StringUtils.substringAfter(tranReqsonArr[3], "办理意见：");
				date = date2;
				date2 = null;
				try {
					date2 = DateUtil.parse(dateStr, "yyyy-MM-dd HH:mm");
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				procTranRecord = new ProcTranRecord();
				procTranRecord.setTransactor(person);
				procTranRecord.setReceiveTime(date);
				procTranRecord.setTransactTime(date2);

				procTranRecord.setTransactInfo(str);
				procTranRecord.setProcessConfig(processConfig);
				procTranRecord.setProcessStep(resPonseNode);
				procTranRecord.setProcinstId(processStatus.getId());
				procTranRecord.setState(TaskService.TASK_STATE_END);
				procTranRecord.setTaskType(TaskService.TASK_TYPE[0][0]);
				save(procTranRecord);

				// 7

				dateStr = tranReqsonArr[4].substring(0, 16);
				str = StringUtils.substringAfter(tranReqsonArr[4], "办理意见：");
				date = date2;
				date2 = null;
				try {
					date2 = DateUtil.parse(dateStr, "yyyy-MM-dd HH:mm");
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				procTranRecord = new ProcTranRecord();
				procTranRecord.setTransactor(person);
				procTranRecord.setReceiveTime(date);
				procTranRecord.setTransactTime(date2);

				procTranRecord.setTransactInfo(str);
				procTranRecord.setProcessConfig(processConfig);
				procTranRecord.setProcessStep(endConfigDetail);
				procTranRecord.setProcinstId(processStatus.getId());
				procTranRecord.setState(TaskService.TASK_STATE_END);
				procTranRecord.setTaskType(TaskService.TASK_TYPE[0][0]);
				save(procTranRecord);

				processStatus.setActId(procTranRecord.getId());
				processStatus.setEnd(date2);
				save(processStatus);
			} else {
				continue;
			}
			if (tranReqsonArr != null && tranReqsonArr.length == 3) {
				TransactProcessStatus processStatus = new TransactProcessStatus();
				processStatus.setBegin(prodAdvReq.getBegin());

				processStatus.setCurrProcessConfig(processConfig);
				processStatus.setCurrProcessConfigDetail(endConfigDetail);
				processStatus.setState(ProcessPvcService.ACTIVE_ROOT);

				save(processStatus);
				prodAdvReq.setProcinstId(processStatus.getId());
				save(prodAdvReq);

				processStatus.setProcinstId(processStatus.getId());

				// 写入任务记录
				String dateStr = tranReqsonArr[0].substring(0, 16);
				Date date = null;
				try {
					date = DateUtil.parse(dateStr, "yyyy-MM-dd HH:mm");
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				ProcTranRecord procTranRecord = new ProcTranRecord();
				procTranRecord.setTransactor(prodAdvReq.getStartPerson());
				procTranRecord.setReceiveTime(date);
				procTranRecord.setTransactTime(date);

				procTranRecord.setTransactInfo("");
				procTranRecord.setProcessConfig(processConfig);
				procTranRecord.setProcessStep(startNode);
				procTranRecord.setProcinstId(processStatus.getId());
				procTranRecord.setState(TaskService.TASK_STATE_END);
				procTranRecord.setTaskType(TaskService.TASK_TYPE[0][0]);
				save(procTranRecord);
				//

				dateStr = tranReqsonArr[1].substring(0, 16);
				Date date2 = null;
				try {
					date2 = DateUtil.parse(dateStr, "yyyy-MM-dd HH:mm");
				} catch (ParseException e) {
					e.printStackTrace();
				}
				ProcTranRecord procTranRecord2 = new ProcTranRecord();
				procTranRecord2.setTransactor(person);
				procTranRecord2.setReceiveTime(date);
				procTranRecord2.setTransactTime(date2);
				procTranRecord2.setTransactInfo("接收需求单");
				procTranRecord2.setProcessConfig(processConfig);
				procTranRecord2.setProcessStep(resPonseNode);
				procTranRecord2.setProcinstId(processStatus.getId());
				procTranRecord2.setState(TaskService.TASK_STATE_END);
				procTranRecord2.setTaskType(TaskService.TASK_TYPE[0][0]);
				save(procTranRecord2);
				dateStr = tranReqsonArr[2].substring(0, 16);
				Date date3 = null;
				try {
					date3 = DateUtil.parse(dateStr, "yyyy-MM-dd HH:mm");
				} catch (ParseException e) {
					e.printStackTrace();
				}
				ProcTranRecord procTranRecord3 = new ProcTranRecord();
				procTranRecord3.setTransactor(person);
				procTranRecord3.setReceiveTime(date2);
				procTranRecord3.setTransactTime(date3);
				str = StringUtils.substringAfter(tranReqsonArr[2], "办理意见：");
				procTranRecord3.setTransactInfo(str);
				procTranRecord3.setProcessConfig(processConfig);
				procTranRecord3.setProcessStep(endConfigDetail);
				procTranRecord3.setProcinstId(processStatus.getId());
				procTranRecord3.setState(TaskService.TASK_STATE_END);
				procTranRecord3.setTaskType(TaskService.TASK_TYPE[0][0]);
				save(procTranRecord3);

				processStatus.setActId(procTranRecord3.getId());
				processStatus.setEnd(date3);
				save(processStatus);

			} else if (tranReqsonArr != null && tranReqsonArr.length == 5) {
				TransactProcessStatus processStatus = new TransactProcessStatus();
				processStatus.setBegin(prodAdvReq.getBegin());

				processStatus.setCurrProcessConfig(processConfig);
				processStatus.setCurrProcessConfigDetail(endConfigDetail);
				processStatus.setState(ProcessPvcService.ACTIVE_ROOT);

				save(processStatus);
				prodAdvReq.setProcinstId(processStatus.getId());
				save(prodAdvReq);
				processStatus.setProcinstId(processStatus.getId());
				// 写入任务记录
				String dateStr = tranReqsonArr[0].substring(0, 16);
				Date date = null;
				try {
					date = DateUtil.parse(dateStr, "yyyy-MM-dd HH:mm");
				} catch (ParseException e) {
					e.printStackTrace();
				}
				ProcTranRecord procTranRecord = new ProcTranRecord();
				procTranRecord.setTransactor(prodAdvReq.getStartPerson());
				procTranRecord.setReceiveTime(date);
				procTranRecord.setTransactTime(date);

				procTranRecord.setTransactInfo("");
				procTranRecord.setProcessConfig(processConfig);
				procTranRecord.setProcessStep(startNode);
				procTranRecord.setProcinstId(processStatus.getId());
				procTranRecord.setState(TaskService.TASK_STATE_END);
				procTranRecord.setTaskType(TaskService.TASK_TYPE[0][0]);
				save(procTranRecord);
				dateStr = tranReqsonArr[1].substring(0, 16);
				Date date2 = null;
				str = StringUtils.substringAfter(tranReqsonArr[1], "办理意见：");
				try {
					date2 = DateUtil.parse(dateStr, "yyyy-MM-dd HH:mm");
				} catch (ParseException e) {
					e.printStackTrace();
				}
				ProcTranRecord procTranRecord2 = new ProcTranRecord();
				procTranRecord2.setTransactor(person);
				procTranRecord2.setReceiveTime(date);
				procTranRecord2.setTransactTime(date2);
				procTranRecord2.setMatters("回退需求单");
				procTranRecord2.setTransactInfo(str);
				procTranRecord2.setProcessConfig(processConfig);
				procTranRecord2.setProcessStep(resPonseNode);
				procTranRecord2.setProcinstId(processStatus.getId());
				procTranRecord2.setState(TaskService.TASK_STATE_END);
				procTranRecord2.setTaskType(TaskService.TASK_TYPE[0][0]);
				save(procTranRecord2);

				// 再次提交需求单
				date = date2;
				dateStr = tranReqsonArr[2].substring(0, 16);
				date2 = null;
				str = StringUtils.substringAfter(tranReqsonArr[2], "办理意见：");
				try {
					date2 = DateUtil.parse(dateStr, "yyyy-MM-dd HH:mm");

				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				ProcTranRecord procTranRecord11 = new ProcTranRecord();
				procTranRecord11.setTransactor(prodAdvReq.getStartPerson());
				procTranRecord11.setReceiveTime(date);
				procTranRecord11.setTransactTime(date2);

				procTranRecord11.setTransactInfo(str);
				procTranRecord11.setProcessConfig(processConfig);
				procTranRecord11.setProcessStep(startNode);
				procTranRecord11.setProcinstId(processStatus.getId());
				procTranRecord11.setState(TaskService.TASK_STATE_END);
				procTranRecord11.setTaskType(TaskService.TASK_TYPE[0][0]);
				save(procTranRecord11);

				// 再次接收需求单
				date = date2;
				dateStr = tranReqsonArr[3].substring(0, 16);

				str = StringUtils.substringAfter(tranReqsonArr[3], "办理意见：");
				try {
					date2 = DateUtil.parse(dateStr, "yyyy-MM-dd HH:mm");

				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				ProcTranRecord procTranRecord22 = new ProcTranRecord();
				procTranRecord22.setTransactor(person);
				procTranRecord22.setReceiveTime(date);
				procTranRecord22.setTransactTime(date2);
				procTranRecord22.setTransactInfo(str);
				procTranRecord22.setProcessConfig(processConfig);
				procTranRecord22.setProcessStep(resPonseNode);
				procTranRecord22.setProcinstId(processStatus.getId());
				procTranRecord22.setState(TaskService.TASK_STATE_END);
				procTranRecord22.setTaskType(TaskService.TASK_TYPE[0][0]);
				save(procTranRecord22);

				dateStr = tranReqsonArr[4].substring(0, 16);
				date = date2;
				try {
					date2 = DateUtil.parse(dateStr, "yyyy-MM-dd HH:mm");

				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				ProcTranRecord procTranRecord3 = new ProcTranRecord();
				procTranRecord3.setTransactor(person);
				procTranRecord3.setReceiveTime(date);
				procTranRecord3.setTransactTime(date2);

				str = StringUtils.substringAfter(tranReqsonArr[4], "办理意见：");
				procTranRecord3.setTransactInfo(str);
				procTranRecord3.setProcessConfig(processConfig);
				procTranRecord3.setProcessStep(endConfigDetail);
				procTranRecord3.setProcinstId(processStatus.getId());
				procTranRecord3.setState(TaskService.TASK_STATE_END);
				procTranRecord3.setTaskType(TaskService.TASK_TYPE[0][0]);
				save(procTranRecord3);
				processStatus.setEnd(date2);
				processStatus.setActId(procTranRecord3.getId());
				save(processStatus);

			}
			System.out.println(tranReason);
			System.out.println(">>>length:" + tranReqsonArr.length);

		}
	}

	@SuppressWarnings("unchecked")
	public List<Map<String, String>> getTypes(String procinst_type) {
		List<Map<String, String>> list = null;
		if ("01".equals(procinst_type)) {
			list = this
					.getJdbcTemplate()
					.queryForList(
							"select t.name as type from t_sys_dict t where t.dict_type =12 order by t.id");
		} else if ("02".equals(procinst_type)) {
			list = this
					.getJdbcTemplate()
					.queryForList(
							"select t.name as type from t_sys_dict t where t.dict_type =15 order by t.id");
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Map<String, String>> getDaiTypes(String procinst_type) {
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		if (procinst_type != null && !"".equals(procinst_type)) {
			if ("01".equals(procinst_type)) {
				list = this
						.getJdbcTemplate()
						.queryForList(
								"select t.name as dai_type from t_sys_dict t where t.dict_type =17 order by t.id");
			} else if ("02".equals(procinst_type)) {
				list = this
						.getJdbcTemplate()
						.queryForList(
								"select t.name as dai_type from t_sys_dict t where t.dict_type =18 order by t.id");
			}
			// list=this.getJdbcTemplate().queryForList("select distinct
			// a.dai_Type from t_business_businessrequest a where
			// a.procinst_type='"+procinst_type+"' and a.dai_Type is not null");
		} else {
			// list=this.getJdbcTemplate().queryForList("select distinct
			// a.dai_Type from t_business_businessrequest a where a.dai_Type is
			// not null");
			list = this
					.getJdbcTemplate()
					.queryForList(
							"select distinct a.name as dai_type from (select  t.name,t.id as type from t_sys_dict t where t.dict_type in(17,18) order by t.id)a order by a.name");
		}
		return list;
	}

	public Organization getOrganizationByCodeId(String codeId){
		String hql="select t from Organization t where t.codeID=?";
		List list=getHibernateTemplate().find(hql, new Object[]{codeId});
		if(list!=null&&list.size()>0){
			return (Organization)list.get(0);
		}
		return null;
	}
	
	public BusinessReason findBusinessReason(String procInstId) {
		// TODO Auto-generated method stub
		String hql="select t from BusinessReason t where t.procinstId=? and t.createType=? order by t.id desc";
		List list=getHibernateTemplate().find(hql, new Object[]{procInstId,"结束"});
		if(list!=null&&list.size()>0){
			return (BusinessReason)list.get(0);
		}
		
		return null;
	}
	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}


}
